﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WS.EKA.Business.Model;
using Oracle.DataAccess.Client;
using WS.EKA.Business.Utilty;

namespace WS.EKA.Business.Dal
{
    public class IncidentAccess
    {
        static IDB DBHelper = DBFactory.CreateBODB();

        public static List<TB_INCIDENT_TEMP> GetImportantIncidentByCondition(int count, int pageIndex)
        {
            var parameters = new List<OracleParameter>(2);
            parameters.Add(new OracleParameter("EndIndex", (pageIndex + 1) * count));
            parameters.Add(new OracleParameter("StartIndex", count * pageIndex));
            var table = DBHelper.ExecuteDataTable(@"select Incident_Number,Event_Source,Category,SubCategory,Impact,Business_System_Type,Status,Assignee,Description,Create_Date  from (
                                                    SELECT ROW_.*, ROWNUM ROWNUM_
                                                    from(SELECT Incident_Number,Event_Source,Category,SubCategory,Priority as Impact,Business_System_Type,Status,Assignee,Description ,Create_Date
                                                          FROM TB_INCIDENT WHERE Priority='High' 
                                                          and (Status='Logged' or Status='Assigned' or Status='Active' or Status='Reopen' or Status='Pending')
                                                          --and (sysdate-Create_Date)*24<=24 
                                                          order by Create_Date desc
                                                        )ROW_ WHERE ROWNUM <= :EndIndex
                                                      )
                                                      WHERE ROWNUM_ >:StartIndex", parameters.ToArray());
            return table.ToList<TB_INCIDENT_TEMP>();
        }

        public static List<TB_INCIDENT_TEMP> GetLatestIncidentByCondition(int count, int pageIndex)
        {
            var parameters = new List<OracleParameter>(2);
            int endIndex = (pageIndex + 1) * count;
            parameters.Add(new OracleParameter("EndIndex", endIndex < 20 ? endIndex : 20));
            parameters.Add(new OracleParameter("StartIndex", count * pageIndex));
            var table = DBHelper.ExecuteDataTable(@"SELECT Incident_Number,Event_Source,Category,SubCategory,Impact,Business_System_Type,Status,Assignee,Description,Create_Date  FROM (
                                                    SELECT ROW_.*, ROWNUM ROWNUM_
                                                              FROM (Select Incident_Number,Event_Source,Category,SubCategory,Priority as  Impact,Business_System_Type,Status,Assignee,Description,Create_Date
                                                              FROM tb_incident where Status='Logged' or Status='Assigned' or Status='Active' or Status='Reopen' or Status='Pending'
                                                             order by Create_Date desc
                                                             ) ROW_ WHERE ROWNUM <= :EndIndex
                                                            ) WHERE ROWNUM_ > :StartIndex", parameters.ToArray());
            return table.ToList<TB_INCIDENT_TEMP>();
        }

        public static IncidentStatisticsModel GetIncidentHistoryNotResolvedStatstics()
        {
            var table = DBHelper.ExecuteDataTable(@"select cast(NotResolved as  NUMBER(9,0)) as Item1 ,cast((select count(INCIDENT_NUMBER) from tb_incident) as  NUMBER(9,0)) Item2 from
                                                    (
                                                    select count(INCIDENT_NUMBER) NotResolved
                                                    from tb_incident where status  in('Logged','Assigned','Active','Pending','Reopen')
                                                    ) T");
            return table.ToList<IncidentStatisticsModel>().FirstOrDefault();
        }

        public static IncidentStatisticsModel GetIncidentStatsticsByMonth()
        {
            var model = new IncidentStatisticsModel();
            List<int> counts = new List<int>(2); 
            using (var reader = DBHelper.ExecuteReader(@"select cast(count(1) as number(8)) from tb_incident where to_char(create_date,'yyyy-mm')=to_char(add_months(sysdate,-1),'yyyy-mm')  
                                                        union all
                                                        select cast(count(1) as number(8)) from tb_incident where to_char(create_date,'yyyy-mm')=to_char(sysdate,'yyyy-mm')"))
            {
                while (reader.Read())
                {
                    counts.Add((int)reader.GetDecimal(0));
                }
            }
            model.Item1 = counts[0];
            model.Item2 = counts[1];
            return model;
        }

        public static List<TodayStatusIncidentForUI> GetTodayStatusIncident()
        {
            var table = DBHelper.ExecuteDataTable(@"select status Category,cast(count(*) as NUMBER(9,0)) Count from tb_Incident where to_char(create_date,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd') and status is not null group by status");
            return table.ToList<TodayStatusIncidentForUI>();
        }

        public static List<TodayIncidentByFaultForUI> GetTodayIncidentByFault()
        {
            var table = DBHelper.ExecuteDataTable(@"select category,cast(count(*) as NUMBER(9,0)) count from tb_Incident where to_char(create_date,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd') and category is not null group by category ");
            return table.ToList<TodayIncidentByFaultForUI>();
        }

        public static TodayIncidentResolvedPercentForUI GetTodayIncidentResolvedPercent()
        {
            var table = DBHelper.ExecuteDataTable(@"select cast((select count(*)  from tb_Incident where to_char(CREATE_DATE,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd') and category is not null) as NUMBER(9,0)) as NewAdd,cast(NewResolved as NUMBER(9,0)) NewResolved from (select count(*) NewResolved from tb_Incident where to_char(CREATE_DATE,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd') and category is not null and (STATUS='Resolved' or STATUS='Closed') and to_char(Modified_Date,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd'))");
            return table.ToList<TodayIncidentResolvedPercentForUI>().FirstOrDefault();
        }

        public static List<_30DaysTrendForUI> Get30DaysTrendIncident()
        {
            var table = DBHelper.ExecuteDataTable(@"select Category,cast(count as number(9,0)) count from
                                                    (
                                                    SELECT ROW_.*, ROWNUM ROWNUM_
                                                      FROM (select * from
                                                            (
                                                             select to_char(create_date,'yyyy-MM-dd') as Category,count(*) count from tb_incident group by to_char(create_date,'yyyy-MM-dd') 
                                                            ) where round(TO_NUMBER(to_date(to_char(sysdate,'yyyy-mm-dd'), 'yyyy-mm-dd')-to_date(Category,'yyyy-mm-dd ')))<30 
                                                            order by Category desc) ROW_
                                                     )");
            return table.ToList<_30DaysTrendForUI>();
        }

        public static List<_30DaysDistributeForUI> Get30DaysDistributeIncident()
        {
            var table = DBHelper.ExecuteDataTable(@"select category,cast(count(*) as NUMBER(9,0)) count from tb_Incident where (sysdate-create_date)<=30 and category is not null group by category");
            return table.ToList<_30DaysDistributeForUI>();
        }
    }
}
